﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Configuration;
using System.Web;
using MySql.Data.MySqlClient;
using System.Data;
using System.ComponentModel.DataAnnotations;

namespace DetetiveKGB.Models
{
    public class ActorsModel
    {
        public int ActorId { get; set; }
        
        [Required(ErrorMessage="O nome do personagem é obrigatório.")]
        [StringLength(100, ErrorMessage="O tamanho máximo do nome é de 100 caracteres.")]
        public string Name { get; set; }
        public string Description { get; set; }
        public string Image { get; set; }
        public bool Enabled { get; set; }

        public void Add()
        {
            MySqlConnection cn = new MySqlConnection(ConfigurationManager.ConnectionStrings["MySQL"].ConnectionString);
            MySqlCommand cm = new MySqlCommand(SqlResources.SqlActor.Add, cn);
            try
            {
                cn.Open();
                cm.Parameters.AddWithValue("name", this.Name);
                cm.Parameters.AddWithValue("description", this.Description);
                cm.Parameters.AddWithValue("image", this.Image);
                cm.Parameters.AddWithValue("enabled", this.Enabled);
                cm.ExecuteNonQuery();
            }
                catch (MySqlException e)
            {
                throw e;
            }
            finally
            {
                cm.Dispose();
                cn.Dispose();
            }
        }
        public void Update()
        {
            MySqlConnection cn = new MySqlConnection(ConfigurationManager.ConnectionStrings["MySQL"].ConnectionString);
            MySqlCommand cm = new MySqlCommand(SqlResources.SqlActor.Update, cn);
            try
            {
                cn.Open();
                cm.Parameters.AddWithValue("actor", this.ActorId);
                cm.Parameters.AddWithValue("name", this.Name);
                cm.Parameters.AddWithValue("description", this.Description);
                cm.Parameters.AddWithValue("image", this.Image);
                cm.Parameters.AddWithValue("enabled", this.Enabled);
                cm.ExecuteNonQuery();
            }
            catch (MySqlException e)
            {
                throw e;
            }
            finally
            {
                cm.Dispose();
                cn.Dispose();
            }
        }
        public static ActorsModel Get(int actorId)
        {
            ActorsModel result = new ActorsModel();
            MySqlConnection cn = new MySqlConnection(ConfigurationManager.ConnectionStrings["MySQL"].ConnectionString);
            MySqlCommand cm = new MySqlCommand(SqlResources.SqlActor.Get, cn);
            MySqlDataReader dr = null;
            try
            {
                cn.Open();
                cm.Parameters.AddWithValue("actor", actorId);
                dr = cm.ExecuteReader(CommandBehavior.SingleRow);

                dr.Read();           
                result.ActorId      = dr.GetInt32(0);
                result.Name         = dr.GetString(1);
                result.Description  = dr.GetString(2);
                result.Image        = dr.GetString(3);
                result.Enabled      = dr.GetBoolean(4);
            }
            catch (MySqlException e)
            {
                throw e;
            }
            finally
            {
                dr.Close();
                cm.Dispose();
                cn.Dispose();
            }
            return result;
        }
        public static List<ActorsModel> ListAll()
        {
            List<ActorsModel> result = new List<ActorsModel>();
            MySqlConnection cn = new MySqlConnection(ConfigurationManager.ConnectionStrings["MySQL"].ConnectionString);
            MySqlCommand cm = new MySqlCommand(SqlResources.SqlActor.ListAll, cn);
            MySqlDataReader dr = null;
            try
            {
                cn.Open();
                dr = cm.ExecuteReader();

                while (dr.Read())
                {
                    ActorsModel am = new ActorsModel()
                    {
                        ActorId     = dr.GetInt32(0),
                        Name        = dr.GetString(1),
                        Description = dr.GetString(2),
                        Enabled     = dr.GetBoolean(3)
                    };
                    result.Add(am);
                }
            }
            catch (MySqlException e)
            {
                throw e;
            }
            finally
            {
                dr.Close();
                cm.Dispose();
                cn.Dispose();
            }
            return result;
        }

        public static bool Exists(int actorId, string name)
        {
            bool exists = false;

            MySqlConnection cn  = new MySqlConnection(ConfigurationManager.ConnectionStrings["MySQL"].ConnectionString);
            MySqlCommand cm     = new MySqlCommand(SqlResources.SqlActor.Exists, cn);
            try
            {
                cn.Open();
                cm.Parameters.AddWithValue("actor", actorId);
                cm.Parameters.AddWithValue("name", name);
                object result = cm.ExecuteScalar();
                try
                {
                    exists = Convert.ToBoolean(result);
                }
                catch 
                {
                    return false;
                }
            }
            catch (MySqlException e)
            {
                throw e;
            }
            finally
            {
                cm.Dispose();
                cn.Dispose();
            }
            return exists;
        }
    }
}